package com.wdt.data.util;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.wdt.data.model.*;
import com.wdt.data.service.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Component
public class PoiUtil {

//    @Autowired
//    CzAreaService czAreaService;

    private static Map company = new HashMap();
    private Map city = new HashMap();
    private Map<String, Integer> heatSourceMap = new HashMap<>();
    private Map<String, Integer> heatStationMap = new HashMap<>();
    private Map<String, Integer> communityMap = new HashMap<>();
    private Map<String, Integer> crewMap = new HashMap<>();
    private Map<String,Integer> buildMap = new HashMap<>();

    public void readExcelFileForHeatSource(InputStream inputStream, String fileName) {
        IBasHeatsourceService heatsourceService = SpringContextUtils.getBean(IBasHeatsourceService.class);
        getCompany();

        List<BasHeatsource> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHeatsource> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHeatsource heatsource = new BasHeatsource();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    heatsource.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    heatsource.setHeatSourceName(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(2).getStringCellValue()) == null || "".equals(company.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应供热企业");
                        return;
                    }
                    heatsource.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(2).getStringCellValue()).toString()));
                    if (row.getCell(4) == null || "".equals(row.getCell(4))) {
                        System.out.println("导入失败第" + j + "行，第4个数据错误");
                        return;
                    }
                    heatsource.setRegionCode(city.get(row.getCell(4).getStringCellValue()).toString());
                    heatsource.setAddress(row.getCell(5).getStringCellValue());
                    heatsource.setHeatingArea(row.getCell(6).getStringCellValue());
                    heatsource.setHeatingCapacityRated(row.getCell(7).getStringCellValue());
                    heatsource.setEnergyType(row.getCell(8).getStringCellValue());
                    heatsource.setDutyCalls(row.getCell(16).getStringCellValue());
                    //查询是否已有数据 有则更新
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", heatsource.getCode());
                    BasHeatsource source = heatsourceService.getOne(queryWrapper);
                    if (source != null) {
                        heatsource.setId(source.getId());
                    }
                    list.add(heatsource);
//                    if (row == null) {
//                        continue;
//                    }
//
//                    //获取一个Row有多少Cell
//
//                    short lastCellNum = row.getLastCellNum();
//                    for (int k = 0; k <= lastCellNum; k++) {
//                        if (row.getCell(k) == null || "".equals(row.getCell(k))) {
//                            continue;
//                        }
//                        row.getCell(k).setCellType(Cell.CELL_TYPE_STRING);
//                        String res = row.getCell(k).getStringCellValue().trim();
//
//                        System.out.println();
//                        //打印出cell(单元格的内容)
//                        sb.append(res + ",");
//                    }
//                    System.out.println(sb.substring(0, sb.length() - 1));
//                    sb = new StringBuffer("");
                }
                heatsourceService.saveOrUpdateBatch(list);
                System.out.println("=========热源共更新"+list.size()+"数据==========");
            }
        }


    }

    public void readExcelFileForHeatSourceExit(InputStream inputStream, String fileName) {
        IBasHeatsourceExitService exitService = SpringContextUtils.getBean(IBasHeatsourceExitService.class);
        getHeatSource();
        List<BasHeatsourceExit> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHeatsourceExit> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHeatsourceExit exit = new BasHeatsourceExit();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    exit.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    exit.setHeatsourceExitName(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatSourceMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatSourceMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应热源");
                        return;
                    }
                    exit.setHeatsourceId(heatSourceMap.get(row.getCell(2).getStringCellValue()));
                    exit.setAddress(row.getCell(3).getStringCellValue());
                    exit.setHeatingArea(row.getCell(4).getStringCellValue());
                    exit.setAreaResident(row.getCell(5).getStringCellValue());
                    exit.setAreaPublic(row.getCell(6).getStringCellValue());
                    exit.setAreaRself(row.getCell(7).getStringCellValue());
                    //查询是否已有数据 有则更新
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", exit.getCode());
                    BasHeatsourceExit basHeatsourceExit = exitService.getOne(queryWrapper);
                    if (basHeatsourceExit != null) {
                        exit.setId(basHeatsourceExit.getId());
                    }
                    list.add(exit);
                }
                exitService.saveOrUpdateBatch(list);
                System.out.println("=========热源出口共更新"+list.size()+"数据==========");
            }
        }


    }
    /**
     * 换热站
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForHeatStation(InputStream inputStream, String fileName) {
        IBasHeatstationService basHeatstationService = SpringContextUtils.getBean(IBasHeatstationService.class);
        getHeatSource();
        getCompany();
        List<BasHeatstation> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHeatstation> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            StringBuffer sb = new StringBuffer();
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHeatstation heatstation = new BasHeatstation();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    heatstation.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    heatstation.setHeatstationName(row.getCell(1).getStringCellValue());

                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatSourceMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatSourceMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应热源");
                        return;
                    }
                    heatstation.setHeatsourceId(heatSourceMap.get(row.getCell(2).getStringCellValue()));
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(3).getStringCellValue()) == null || "".equals(company.get(row.getCell(3).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第3个数据无对应供热企业");
                        return;
                    }
                    heatstation.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(3).getStringCellValue()).toString()));
                    heatstation.setAddress(row.getCell(6).getStringCellValue());
                    heatstation.setMapx(row.getCell(7).getStringCellValue());
                    heatstation.setMapy(row.getCell(8).getStringCellValue());
                    heatstation.setAreaResident(row.getCell(9).getStringCellValue());
                    heatstation.setAreaPublic(row.getCell(10).getStringCellValue());
                    heatstation.setAreaRself(row.getCell(11).getStringCellValue());
                    heatstation.setHeatingArea(row.getCell(12).getStringCellValue());
                    if (row.getCell(13).getStringCellValue() == null || "".equals(row.getCell(13).getStringCellValue())) {
                        System.out.println("导入失败第" + j + "行，第14个数据错误");
                        return;
                    }
                    heatstation.setHouseHoldCount(Integer.parseInt(row.getCell(13).getStringCellValue()));
                    heatstation.setHeatTransferAbility(row.getCell(14).getStringCellValue());
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code", heatstation.getCode());
                    BasHeatstation basHeatstation = basHeatstationService.getOne(queryWrapper);
                    if (basHeatstation != null){
                        heatstation.setId(basHeatstation.getId());
                    }
                    list.add(heatstation);
                }
                basHeatstationService.saveOrUpdateBatch(list);
                System.out.println("=========换热站共更新"+list.size()+"数据==========");
            }
        }


    }

    /**
     * 机组
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForCrew(InputStream inputStream, String fileName) {
        IBasCrewService crewService = SpringContextUtils.getBean(IBasCrewService.class);
        getHeatStation();
        getCompany();
        List<BasCrew> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasCrew> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasCrew crew = new BasCrew();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    crew.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    crew.setCrewName(row.getCell(1).getStringCellValue());

                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(2).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(2).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第2个数据无对应换热站");
                        return;
                    }
                    crew.setHeatstationId(heatStationMap.get(row.getCell(2).getStringCellValue()));
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(3).getStringCellValue()) == null || "".equals(company.get(row.getCell(3).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第3个数据无对应供热企业");
                        return;
                    }
                    crew.setHeatCompanyId(Integer.parseInt(company.get(row.getCell(3).getStringCellValue()).toString()));
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code",crew.getCode());
                    BasCrew basCrew = crewService.getOne(queryWrapper);
                    if (basCrew != null){
                        crew.setId(basCrew.getId());
                    }
                    System.out.println("========i="+j);
                    list.add(crew);
                }
                crewService.saveOrUpdateBatch(list);
                System.out.println("=========机组共更新"+list.size()+"数据==========");
            }
        }


    }

    /**
     * 小区
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForCommunity(InputStream inputStream, String fileName) {
        IBasCommunityService communityService = SpringContextUtils.getBean(IBasCommunityService.class);
        getHeatStation();
        getCompany();
        List<BasCommunity> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasCommunity> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasCommunity community = new BasCommunity();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    community.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    community.setCommunityName(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    community.setCommunityType(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    community.setHeatingArea(row.getCell(3).getStringCellValue());
                    if (row.getCell(4) == null || "".equals(row.getCell(4))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    community.setHouseHoldCount(Integer.parseInt(row.getCell(4).getStringCellValue()));
                    if (row.getCell(8) == null || "".equals(row.getCell(8))) {
                        System.out.println("导入失败第" + j + "行，第8个数据错误");
                        return;
                    }
                    community.setRegionCode(city.get(row.getCell(8).getStringCellValue()).toString());
                    if (row.getCell(9) == null || "".equals(row.getCell(9))) {
                        System.out.println("导入失败第" + j + "行，第9个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(9).getStringCellValue()) == null || "".equals(company.get(row.getCell(9).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第9个数据无对应供热企业");
                        return;
                    }
                    community.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(9).getStringCellValue()).toString()));
                    if (row.getCell(10) == null || "".equals(row.getCell(10))) {
                        System.out.println("导入失败第" + j + "行，第10个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(10).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(10).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第10个数据无对应换热站");
                        return;
                    }
                    community.setHeatstationId(heatStationMap.get(row.getCell(10).getStringCellValue()));
                    community.setMapx(row.getCell(11).getStringCellValue());
                    community.setMapy(row.getCell(12).getStringCellValue());
                    community.setAddress(row.getCell(13).getStringCellValue());
                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code",community.getCode());
                    BasCommunity basCommunity = communityService.getOne(queryWrapper);
                    if (basCommunity != null){
                        community.setId(basCommunity.getId());
                    }
                    list.add(community);
                }
                communityService.saveOrUpdateBatch(list);
                System.out.println("=========小区共更新"+list.size()+"数据==========");
            }
        }


    }

    /**
     * 用热建筑
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForBuilding(InputStream inputStream, String fileName) {
        IBasBuildingService basBuildingService = SpringContextUtils.getBean(IBasBuildingService.class);
        getHeatStation();
        getCompany();
        getCommunity();
        getCrew();
        List<BasBuilding> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasBuilding> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasBuilding building = new BasBuilding();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    building.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    building.setBuildingName(row.getCell(1).getStringCellValue());
                    if (row.getCell(2) == null || "".equals(row.getCell(2))) {
                        System.out.println("导入失败第" + j + "行，第2个数据错误");
                        return;
                    }
                    building.setBuildingType(row.getCell(2).getStringCellValue());
                    if (row.getCell(3) == null || "".equals(row.getCell(3))) {
                        System.out.println("导入失败第" + j + "行，第3个数据错误");
                        return;
                    }
                    building.setHeatingArea(row.getCell(3).getStringCellValue());
                    building.setEnergyConsumptionType(row.getCell(4).getStringCellValue());
                    building.setBuildYear(row.getCell(5).getStringCellValue());

                    if (row.getCell(6) == null || "".equals(row.getCell(6))) {
                        System.out.println("导入失败第" + j + "行，第8个数据错误");
                        return;
                    }
                    if (communityMap.get(row.getCell(6).getStringCellValue()) == null || "".equals(communityMap.get(row.getCell(6).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第6个数据无对应小区");
                        return;
                    }
                    building.setCommunityId(communityMap.get(row.getCell(6).getStringCellValue()));

                    /*if (row.getCell(7) == null || "".equals(row.getCell(7))) {
                        System.out.println("导入失败第" + j + "行，第7个数据错误");
                        return;
                    }
                    if (crewMap.get(row.getCell(7).getStringCellValue()) == null || "".equals(crewMap.get(row.getCell(7).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第7个数据无对应机组");
                        return;
                    }*/
                    if (row.getCell(7) == null || "".equals(row.getCell(7))){

                    }else{
                        building.setCrewId(crewMap.get(row.getCell(7).getStringCellValue()));
                    }
                    if (row.getCell(8) == null || "".equals(row.getCell(8))) {
                        System.out.println("导入失败第" + j + "行，第8个数据错误");
                        return;
                    }
                    if (heatStationMap.get(row.getCell(8).getStringCellValue()) == null || "".equals(heatStationMap.get(row.getCell(8).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第8个数据无对应换热站");
                        return;
                    }
                    building.setHeatstationId(heatStationMap.get(row.getCell(8).getStringCellValue()));
                    if (row.getCell(9) == null || "".equals(row.getCell(9))) {
                        System.out.println("导入失败第" + j + "行，第9个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(9).getStringCellValue()) == null || "".equals(company.get(row.getCell(9).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第9个数据无对应供热企业");
                        return;
                    }
                    building.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(9).getStringCellValue()).toString()));
                    building.setAddress(row.getCell(12).getStringCellValue());
                    building.setBuildingStructure(row.getCell(13).getStringCellValue());
                    building.setIsEnergysaving(row.getCell(14).getStringCellValue() == "是" ? 1 : 0);
                    building.setIsSeparateControl(row.getCell(15).getStringCellValue() == "是" ? 1 : 0);
                    building.setIsMeasure(row.getCell(16).getStringCellValue() == "是" ? 1 : 0);
                    building.setIsHeatMeteringTransformed(row.getCell(17).getStringCellValue() == "是" ? "1" : "0");
                    building.setHeatMeteringDate(row.getCell(18).getStringCellValue());

                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code",building.getCode());
                    BasBuilding basBuilding = basBuildingService.getOne(queryWrapper);
                    if (basBuilding != null){
                        building.setId(basBuilding.getId());
                    }
                    list.add(building);
                }
                basBuildingService.saveOrUpdateBatch(list);
                System.out.println("=========用热建筑共更新"+list.size()+"数据==========");
            }
        }


    }

    /**
     * 热用户
     *
     * @param inputStream
     * @param fileName
     */
    public void readExcelFileForHouseHolder(InputStream inputStream, String fileName) {
        IBasHouseholdService householdService = SpringContextUtils.getBean(IBasHouseholdService.class);
        getCompany();
        getCommunity();
        getCrew();
        getBuilding();
        List<BasHousehold> list = new ArrayList<>();
        /**
         * 然后再读取文件的时候，应该excel文件的后缀名在不同的版本中对应的解析类不一样
         * 要对fileName进行后缀的解析
         */
        Workbook workbook = null;
        try {
            //判断什么类型文件
            if (fileName.endsWith(".xls")) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (fileName.endsWith(".xlsx")) {
                workbook = new XSSFWorkbook(inputStream);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (workbook == null) {
            return;
        } else {
            QueryWrapper<BasHousehold> queryWrapper = null;
            //获取所有的工作表的的数量
            int numOfSheet = workbook.getNumberOfSheets();
            System.out.println(numOfSheet + "--->numOfSheet");
            //遍历表
            for (int i = 0; i < numOfSheet; i++) {
                //获取一个sheet也就是一个工作本。
                Sheet sheet = workbook.getSheetAt(i);
                if (sheet == null) continue;
                //获取一个sheet有多少Row
                int lastRowNum = sheet.getLastRowNum();
                if (lastRowNum == 0) continue;
                Row row;
                for (int j = 1; j <= lastRowNum; j++) {
                    row = sheet.getRow(j);
                    BasHousehold household = new BasHousehold();
                    if (row.getCell(0) == null || "".equals(row.getCell(0))) {
                        System.out.println("导入失败第" + j + "行，第0个数据错误");
                        return;
                    }
                    household.setCode(row.getCell(0).getStringCellValue());
                    if (row.getCell(1) == null || "".equals(row.getCell(1))) {
                        System.out.println("导入失败第" + j + "行，第1个数据错误");
                        return;
                    }
                    household.setHouseHolder(row.getCell(1).getStringCellValue());
                    household.setUnitNo(row.getCell(2).getStringCellValue());
                    household.setFloor(row.getCell(3).getStringCellValue());
                    household.setHouseNo(row.getCell(4).getStringCellValue());
                    household.setHeatingArea(row.getCell(5).getStringCellValue());

                    if (row.getCell(6) == null || "".equals(row.getCell(6))) {
                        System.out.println("导入失败第" + j + "行，第6个数据错误");
                        return;
                    }
                    if (buildMap.get(row.getCell(6).getStringCellValue()) == null || "".equals(buildMap.get(row.getCell(6).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第6个数据无对应建筑");
                        return;
                    }
                    household.setBuildingId(buildMap.get(row.getCell(6).getStringCellValue()));

                    if (row.getCell(7) == null || "".equals(row.getCell(7))) {
                        System.out.println("导入失败第" + j + "行，第7个数据错误");
                        return;
                    }
                    if (communityMap.get(row.getCell(7).getStringCellValue()) == null || "".equals(communityMap.get(row.getCell(7).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第7个数据无对应小区");
                        return;
                    }
                    household.setCommunityId(communityMap.get(row.getCell(7).getStringCellValue()));
                    if (row.getCell(9) == null || "".equals(row.getCell(9))) {
                        System.out.println("导入失败第" + j + "行，第9个数据错误");
                        return;
                    }
                    if (company.get(row.getCell(9).getStringCellValue()) == null || "".equals(company.get(row.getCell(9).getStringCellValue()))) {
                        System.out.println("导入失败第" + j + "行，第9个数据无对应供热企业");
                        return;
                    }
                    household.setHeatcompanyId(Integer.parseInt(company.get(row.getCell(9).getStringCellValue()).toString()));
                    household.setContactTel(row.getCell(12).getStringCellValue());
                    household.setHeatingType(row.getCell(13).getStringCellValue());

                    queryWrapper = new QueryWrapper<>();
                    queryWrapper.eq("code",household.getCode());
                    BasHousehold basHousehold = householdService.getOne(queryWrapper);
                    if (basHousehold != null){
                        household.setId(basHousehold.getId());
                    }
                    list.add(household);
                }
                householdService.saveOrUpdateBatch(list);
                System.out.println("=========热用户共更新"+list.size()+"数据==========");
            }
        }


    }
    public void getCompany() {
        IBasHeatcompanyService iBasHeatcompanyService = SpringContextUtils.getBean(IBasHeatcompanyService.class);
        CzAreaService czAreaService = SpringContextUtils.getBean(CzAreaService.class);
        List<BasHeatcompany> list = iBasHeatcompanyService.list();
        for (BasHeatcompany basHeatcompany : list) {
            company.put(basHeatcompany.getHeatCompanyName(), basHeatcompany.getId());
        }
        List<CzArea> areas = czAreaService.list();
        for (CzArea area : areas) {
            city.put(area.getAreaName(), area.getAreaCode());
        }
    }

    public void getHeatSource() {
        IBasHeatsourceService heatsourceService = SpringContextUtils.getBean(IBasHeatsourceService.class);
        List<BasHeatsource> list = heatsourceService.list();
        for (BasHeatsource heatsource : list) {
            heatSourceMap.put(heatsource.getHeatSourceName(), heatsource.getId());
        }
    }

    public void getHeatStation() {
        IBasHeatstationService service = SpringContextUtils.getBean(IBasHeatstationService.class);
        List<BasHeatstation> list = service.list();
        for (BasHeatstation heatstation : list) {
            heatStationMap.put(heatstation.getHeatstationName(), heatstation.getId());
        }
    }

    public void getCommunity() {
        IBasCommunityService communityService = SpringContextUtils.getBean(IBasCommunityService.class);
        List<BasCommunity> list = communityService.list();
        for (BasCommunity community : list) {
            communityMap.put(community.getCommunityName(), community.getId());
        }
    }

    public void getCrew() {
        IBasCrewService crewService = SpringContextUtils.getBean(IBasCrewService.class);
        List<BasCrew> list = crewService.list();
        for (BasCrew crew : list) {
            crewMap.put(crew.getCrewName(), crew.getId());
        }
    }

    public void getBuilding() {
        IBasBuildingService basBuildingService = SpringContextUtils.getBean(IBasBuildingService.class);
        List<BasBuilding> list = basBuildingService.list();
        for (BasBuilding building : list) {
            buildMap.put(building.getBuildingName(),building.getId());
        }
    }
}
